/*
* Conditions Of Use
*
* This software was developed by employees of the Sigmatrix(Beijing) Corporation.
* This software is provided by sigmatrix as a service and is expressly
* provided "AS IS."  Sigmatrix MAKES NO WARRANTY OF ANY KIND, EXPRESS, IMPLIED
* OR STATUTORY, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTY OF
* MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, NON-INFRINGEMENT
* AND DATA ACCURACY.  Sigmatrix does not warrant or make any representations
* regarding the use of the software or the results thereof, including but
* not limited to the correctness, accuracy, reliability or usefulness of
* the software.
*
* Permission to use this software is contingent upon your acceptance
* of the terms of this agreement.
*
*/
package com.sigmatrix.h5.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;

import com.sigmatrix.h5.dao.OutStorageDao;
import com.sigmatrix.h5.dao.impl.common.impl.BaseDaoImpl;
import com.sigmatrix.h5.dto.OutStorageData;
import com.sigmatrix.h5.entity.OutStorage;
import com.sigmatrix.h5.enums.OutStorageStatusEnum;
import com.sigmatrix.h5.enums.TableEnum;
import com.sigmatrix.h5.exception.DaoException;
import com.sigmatrix.h5.utils.Cons;
import com.sigmatrix.h5.utils.DateUtil;

/**
 *@ClassName: OutStorageDaoImpl
 *@Description: 出库单数据库操作接口实现
 *@author youjun
 *@date 2017-06-24 17:33:53
 */
@Repository(value="outStorageDao")
public class OutStorageDaoImpl extends BaseDaoImpl<OutStorage> implements OutStorageDao {
	
	/**注入jdbcTemplate*/
	@Autowired
	private transient JdbcTemplate jdbcTemplate;
	
	/**表名*/
	public static final String TABLE_NAME = "_dealers_out_storage";
	
	private static final Logger LOGGER = LoggerFactory.getLogger(OutStorageDaoImpl.class);

	/**
	 * (non-Javadoc)
	 *@Title: findByPage
	 *@Description: 
	 *@see com.sigmatrix.sc.handheld.inter.dao.OutStorageDao#findByPage(int, int, com.sigmatrix.sc.handheld.inter.entity.OutStorage)
	 *@author youjun
	 *@date 2017年7月8日 上午11:08:16
	 */
	@Override
	public List<OutStorage> findByPage(final int pageNo, final int pageSize, final OutStorage outStorage, 
			final Map<String, Object> otherParamsMap, final String enterpriseCode)
			throws DaoException {

		final StringBuilder sql = new StringBuilder("SELECT * FROM ");
		final int startNo = (pageNo - 1) * pageSize + 1; // 当前起始条数
		final int endNo = pageNo * pageSize; // 当前结束条数
		final List<Object> paramList = new ArrayList<Object>();
		sql.append("(SELECT *, ROW_NUMBER() OVER(ORDER BY a._create_time DESC) AS RowNumber FROM ").append(getTname(enterpriseCode, TABLE_NAME)).append(" as a ");
		sql.append("WHERE a._status <> ?");
		paramList.add(Cons.STATUS_DELETE);
		sql.append(assemblyPageWhereConditon(outStorage, paramList, true, "a", otherParamsMap)); // 追加where条件部分
		sql.append(") as b WHERE RowNumber BETWEEN ? AND ?");
		paramList.add(startNo);
		paramList.add(endNo);
		final Object[] params = paramList.toArray();
		try {
			return jdbcTemplate.query(sql.toString(), params, new OutStorageRowMapper());
		} catch (DataAccessException e) {
			throw new DaoException(e);
		}
	}
	
	

	@Override
	public List<OutStorage> findByPage(int pageNo, int pageSize, OutStorage outStorage,
			Map<String, Object> otherParamsMap, String enterpriseCode, Integer terminalUserId) throws DaoException {

		final StringBuilder sql = new StringBuilder("SELECT * FROM ");
		final int startNo = (pageNo - 1) * pageSize + 1; // 当前起始条数
		final int endNo = pageNo * pageSize; // 当前结束条数
		final List<Object> paramList = new ArrayList<Object>();
		sql.append("(SELECT a.*, ROW_NUMBER() OVER(ORDER BY a._create_time DESC) AS RowNumber FROM ").append(getTname(enterpriseCode, TABLE_NAME)).append(" as a ");
		sql.append("LEFT JOIN ").append(getTname(enterpriseCode, TableEnum.DEALER_OUT_STORAGE_TERMINAL_USER)).append(" ostu ");
		sql.append(" ON a._id = ostu._out_storage_id ");
		sql.append(" WHERE a._status <> ?");
		paramList.add(Cons.STATUS_DELETE);
		sql.append(" AND a._send_dealer_id = ?");
		paramList.add(outStorage.getSendDealerId());
		sql.append(" AND (ostu._terminal_user_id = ? OR a._id NOT IN (SELECT _out_storage_id FROM ");
		sql.append(getTname(enterpriseCode, TableEnum.DEALER_OUT_STORAGE_TERMINAL_USER)).append(" ))");
		paramList.add(terminalUserId);
		sql.append(" AND 1=1");
		sql.append(assemblyPageWhereConditon(outStorage, paramList, true, "a", otherParamsMap)); // 追加where条件部分
		sql.append(") as b WHERE RowNumber BETWEEN ? AND ?");
		paramList.add(startNo);
		paramList.add(endNo);
		final Object[] params = paramList.toArray();
		LOGGER.info("分页查询={}",sql.toString());
		try {
			return jdbcTemplate.query(sql.toString(), params, new OutStorageRowMapper());
		} catch (DataAccessException e) {
			throw new DaoException(e);
		}
	}
	
	/**
	 * (non-Javadoc)
	 *@Title: getCountByCondition
	 *@Description: 
	 *@see com.sigmatrix.sc.handheld.inter.dao.OutStorageDao#getCountByCondition(com.sigmatrix.sc.handheld.inter.entity.OutStorage)
	 *@author youjun
	 *@date 2017年7月8日 上午11:08:12
	 */
	@Override
	public int getCountByCondition(final OutStorage outStorage, final Map<String, Object> otherParamsMap, final String enterpriseCode) throws DaoException {

		final List<Object> paramList = new ArrayList<Object>();
		final StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM ").append(getTname(enterpriseCode, TABLE_NAME));
		sql.append(" WHERE _status <> ?");
		paramList.add(Cons.STATUS_DELETE);
		try {
			sql.append(assemblyWhereConditon(outStorage, paramList, true, "", otherParamsMap)); // 追加where条件部分
			return jdbcTemplate.queryForObject(sql.toString(), paramList.toArray(), Integer.class);
		} catch (DataAccessException e) {
			throw new DaoException(e);
		}
	}


	@Override
	public int getCountByCondition(OutStorage outStorage, Map<String, Object> otherParamsMap, String enterpriseCode,
			Integer terminalUserId) throws DaoException {

		final List<Object> paramList = new ArrayList<Object>();
		final StringBuilder sql = new StringBuilder("SELECT COUNT(a._id) FROM ").append(getTname(enterpriseCode, TABLE_NAME)).append(" as a ");
		sql.append("LEFT JOIN ").append(getTname(enterpriseCode, TableEnum.DEALER_OUT_STORAGE_TERMINAL_USER)).append(" ostu ");
		sql.append("ON a._id = ostu._out_storage_id ");
		sql.append("WHERE a._status <> ? ");
		paramList.add(Cons.STATUS_DELETE);
		sql.append("AND a._send_dealer_id = ? ");
		paramList.add(outStorage.getSendDealerId());
		sql.append(" AND (ostu._terminal_user_id = ? OR a._id NOT IN (SELECT _out_storage_id FROM ");
		sql.append(getTname(enterpriseCode, TableEnum.DEALER_OUT_STORAGE_TERMINAL_USER)).append(" ))");
		paramList.add(terminalUserId);
		sql.append("AND 1=1");
		try {
			sql.append(assemblyWhereConditon(outStorage, paramList, true, "", otherParamsMap)); // 追加where条件部分
			LOGGER.info(sql.toString());
			return jdbcTemplate.queryForObject(sql.toString(), paramList.toArray(), Integer.class);
		} catch (DataAccessException e) {
			throw new DaoException(e);
		}
	}

	/**
	 * (non-Javadoc)
	 *@Title: findById
	 *@Description: 
	 *@see com.sigmatrix.sc.handheld.inter.dao.OutStorageDao#findById(java.lang.Integer, java.lang.String)
	 *@author youjun
	 *@date 2017年6月24日 下午7:50:23
	 */
	@Override
	public OutStorage findById(final Long id, final String enterpriseCode) throws DaoException {
		final StringBuilder sql = new StringBuilder("SELECT * FROM ").append(getTname(enterpriseCode, TABLE_NAME));
		sql.append(" WHERE _id = ?");
		final Object[] params = new Object[]{id};
		final List<OutStorage> list = jdbcTemplate.query(sql.toString(), params, new OutStorageRowMapper());
		if(CollectionUtils.isEmpty(list)) {
			return null;
		}
		return list.get(0);
	}
	
	/**
	 * (non-Javadoc)
	 *@Title: save
	 *@Description: 
	 *@see com.sigmatrix.sc.handheld.inter.dao.OutStorageDao#save(com.sigmatrix.sc.handheld.inter.entity.OutStorage)
	 *@author youjun
	 *@date 2017年6月24日 下午7:50:20
	 */
	@Override
	public long save(final OutStorage outStorage, final String enterpriseCode) throws DaoException {

		final StringBuilder sql = new StringBuilder("INSERT INTO ").append(getTname(enterpriseCode, TABLE_NAME));
		sql.append("(_order_code, _out_storage_time, _creator_id, _create_time, _terminal_id, _dealer_id, _warehouse_id, _status, _actual_number, _plan_number, _actual_sp_num, _plan_sp_num, _fail_number, _fail_sp_num, _update_id, _update_time, _enable_id, _enable_time, _audit_id, _audit_time, _start_time, _end_time, _channel_type, _remarks, _customer_order_code, _customer_type, _customer_id, _products, _to_warehouse_id, _target_channel_id, _other_type_id, _ret_reason, _is_auto_verify, _out_storage_type, _disable_id, _disable_time,_send_dealer_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?)");
		final Object[] params = new Object[]{outStorage.getOrderCode(), outStorage.getOutStorageTime(), outStorage.getCreatorId(), outStorage.getCreateTime(), outStorage.getTerminalId(), outStorage.getDealerId(), outStorage.getWarehouseId(), outStorage.getStatus(), outStorage.getActualNumber(), outStorage.getPlanNumber(), outStorage.getActualSpNum(), outStorage.getPlanSpNum(), outStorage.getFailNumber(), outStorage.getFailSpNum(), outStorage.getUpdateId(), outStorage.getUpdateTime(), outStorage.getEnableId(), outStorage.getEnableTime(), outStorage.getAuditId(), outStorage.getAuditTime(), outStorage.getStartTime(), outStorage.getEndTime(), outStorage.getChannelType(), outStorage.getRemarks(), outStorage.getCustomerOrderCode(), outStorage.getCustomerType(), outStorage.getCustomerId(), outStorage.getProducts(), outStorage.getToWarehouseId(), outStorage.getTargetChannelId(), outStorage.getOtherTypeId(), outStorage.getRetReason(), outStorage.getIsAutoVerify(), outStorage.getOutStorageType(), outStorage.getDisableId(), outStorage.getDisableTime(),outStorage.getSendDealerId()};
		final KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(final Connection con) throws SQLException {
				final PreparedStatement pstmt = con.prepareStatement(sql.toString(), Statement.RETURN_GENERATED_KEYS);
				int index = 1;
				for (final Object value : params) {
					pstmt.setObject(index, value);
					index++;
				}
				return pstmt;
			}
		}, keyHolder);
		return keyHolder.getKey().longValue();
	}

	/**
	 * (non-Javadoc)
	 *@Title: update
	 *@Description: 
	 *@see com.sigmatrix.sc.handheld.inter.dao.OutStorageDao#update(com.sigmatrix.sc.handheld.inter.entity.OutStorage)
	 *@author youjun
	 *@date 2017年6月24日 下午7:50:16
	 */
	@Override
	public int update(final OutStorage outStorage, final String enterpriseCode) throws DaoException {

		final StringBuilder sql = new StringBuilder("UPDATE ").append(getTname(enterpriseCode, TABLE_NAME));
		sql.append(" SET _order_code = ?, _out_storage_time = ?, _creator_id = ?, _create_time = ?, _terminal_id = ?, _dealer_id = ?, _warehouse_id = ?, _status = ?, _actual_number = ?, _plan_number = ?, _actual_sp_num = ?, _plan_sp_num = ?, _fail_number = ?, _fail_sp_num = ?, _update_id = ?, _update_time = ?, _enable_id = ?, _enable_time = ?, _audit_id = ?, _audit_time = ?, _start_time = ?, _end_time = ?, _channel_type = ?, _remarks = ?, _customer_order_code = ?, _customer_type = ?, _customer_id = ?, _products = ?, _to_warehouse_id = ?, _target_channel_id = ?, _other_type_id = ?, _ret_reason = ?, _is_auto_verify = ?, _out_storage_type = ?, _disable_id = ?, _disable_time = ? , _send_dealer_id = ? WHERE _id = ?");
		final Object[] params = new Object[]{outStorage.getOrderCode(), outStorage.getOutStorageTime(), outStorage.getCreatorId(), outStorage.getCreateTime(), outStorage.getTerminalId(), outStorage.getDealerId(), outStorage.getWarehouseId(), outStorage.getStatus(), outStorage.getActualNumber(), outStorage.getPlanNumber(), outStorage.getActualSpNum(), outStorage.getPlanSpNum(), outStorage.getFailNumber(), outStorage.getFailSpNum(), outStorage.getUpdateId(), outStorage.getUpdateTime(), outStorage.getEnableId(), outStorage.getEnableTime(), outStorage.getAuditId(), outStorage.getAuditTime(), outStorage.getStartTime(), outStorage.getEndTime(), outStorage.getChannelType(), outStorage.getRemarks(), outStorage.getCustomerOrderCode(), outStorage.getCustomerType(), outStorage.getCustomerId(), outStorage.getProducts(), outStorage.getToWarehouseId(), outStorage.getTargetChannelId(), outStorage.getOtherTypeId(), outStorage.getRetReason(), outStorage.getIsAutoVerify(), outStorage.getOutStorageType(), outStorage.getDisableId(), outStorage.getDisableTime(), outStorage.getSendDealerId(), outStorage.getId()};
		final int[] argTypes = new int[]{Types.VARCHAR, Types.TIMESTAMP, Types.INTEGER, Types.TIMESTAMP, Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.SMALLINT, Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.TIMESTAMP, Types.INTEGER, Types.TIMESTAMP, Types.INTEGER, Types.TIMESTAMP, Types.TIMESTAMP, Types.TIMESTAMP, Types.SMALLINT, Types.VARCHAR, Types.VARCHAR, Types.SMALLINT, Types.INTEGER, Types.VARCHAR, Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.SMALLINT, Types.SMALLINT, Types.INTEGER, Types.TIMESTAMP, Types.INTEGER, Types.BIGINT};
		return jdbcTemplate.update(sql.toString(), params, argTypes);
	}
	
	/**(non-Javadoc)
	 *@Title: close
	 *@Description: 
	 *@see com.sigmatrix.sc.handheld.inter.dao.OutStorageDao#close(int, java.lang.String)
	 *@author youjun
	 *@date 2017年8月23日 下午4:26:09
	 */
	@Override
	public int close(final long id, final String enterpriseCode) throws DaoException {

		final StringBuilder sql = new StringBuilder("UPDATE ").append(getTname(enterpriseCode, TABLE_NAME));
		sql.append(" SET _status = ?, _update_time = ? WHERE _id = ?");
		final Object[] params = new Object[]{OutStorageStatusEnum.CLOSE.getId(), new Date(), id};
		final int[] argTypes = new int[]{Types.SMALLINT, Types.TIMESTAMP, Types.BIGINT};
		return jdbcTemplate.update(sql.toString(), params, argTypes);
	}
	
	/**
	 * (non-Javadoc)
	 *@Title: findListByCondition
	 *@Description: 
	 *@see com.sigmatrix.sc.handheld.inter.dao.OutStorageDao#findListByCondition(com.sigmatrix.sc.handheld.inter.entity.OutStorage)
	 *@author youjun
	 *@date 2017年6月24日 下午7:50:12
	 */
	@Override
	public List<OutStorage> findListByCondition(final OutStorage outStorage, final String enterpriseCode) throws DaoException {

		final StringBuilder sql = new StringBuilder("SELECT * FROM ").append(getTname(enterpriseCode, TABLE_NAME));
		final List<Object> paramList = new ArrayList<Object>();
		sql.append(" WHERE _status <> ?");
		paramList.add(Cons.STATUS_DELETE);
		sql.append(assemblyWhereConditon(outStorage, paramList, true, "", null));
		sql.append(" ORDER BY _create_time DESC");
		try {
			return jdbcTemplate.query(sql.toString(), paramList.toArray(), new OutStorageRowMapper());
		} catch (DataAccessException e) {
			throw new DaoException(e);
		}
	}

	/**
	 *@Title: assemblyWhereConditon
	 *@Description: 组装where条件部分
	 *@return void
	 *@author youjun
	 *@date 2017年6月24日 下午7:41:33
	 *@param outStorage
	 *@param sql
	 *@param paramList
	 *@param isExistWhereCondition 是否已经存在where条件,true则追加" AND ..."否则追加" WHERE 1=1"
	 *@param alias 别名
	 *@param otherParamMap 
	 * @return 
	 * @throws DaoException 
	 */
	private String assemblyWhereConditon(final OutStorage outStorage, final List<Object> paramList, 
			final boolean isExistWhereCondition, final String alias, Map<String, Object> otherParamMap) throws DaoException {
		
		String aliasName = ""; // 别名+".",eg:a.
		if(StringUtils.isNotBlank(alias)) {
			aliasName = new StringBuilder(alias).append(".").toString();
		}
		if(paramList==null) {
			throw new DaoException("参数列表不能为null");
		}
		StringBuilder whereSql = new StringBuilder();
		if(!isExistWhereCondition) {
			whereSql.append(" WHERE 1=1");
		}
		if(outStorage!=null) {
			if(outStorage.getChannelType()!=null) {
				whereSql.append(" AND ").append(aliasName).append("_channel_type = ?");
				paramList.add(outStorage.getChannelType());
				/*whereSql.append(" AND ").append(aliasName).append("_dealer_id = ?");
				paramList.add(outStorage.getDealerId());
				*/
			/*	whereSql.append(" AND ").append(aliasName).append("_send_dealer_id = ?");
				paramList.add(outStorage.getSendDealerId());*/
			}
			if (outStorage.getStatus() != null) {
				if(outStorage.getStatus().intValue()==OutStorageStatusEnum.SPECIAL_FOR_PDA_SYNC_LIST.getId()) {
					whereSql.append(" AND ").append(aliasName).append("_status in (");
					final List<Integer> specialForPdaSyncList = new ArrayList<Integer>();
					specialForPdaSyncList.add(OutStorageStatusEnum.HAS_AUDIT.getId());
					specialForPdaSyncList.add(OutStorageStatusEnum.PROCEED_ING.getId());
					specialForPdaSyncList.add(OutStorageStatusEnum.EXCEPTION.getId());
					final StringBuilder statusIn = new StringBuilder();
					for(final Integer status:specialForPdaSyncList){
						statusIn.append("?").append(",");
						paramList.add(status);
					}
					whereSql.append(statusIn.substring(0, statusIn.length()-1));
					whereSql.append(")");
				}else{
					whereSql.append(" AND ").append(aliasName).append("_status = ?");
					paramList.add(outStorage.getStatus());
				}
			}
			if (StringUtils.isNotBlank(outStorage.getOrderCode())) {
				whereSql.append(" AND ").append(aliasName).append("_order_code LIKE ?");
				paramList.add(new StringBuilder().append("%").append(outStorage.getOrderCode()).append("%"));
			}
		}
		if(!CollectionUtils.isEmpty(otherParamMap)) {
			final String createTimeStart = (String) otherParamMap.get("createTimeStart");
			final String createTimeEnd = (String) otherParamMap.get("createTimeEnd");
			if(StringUtils.isNotBlank(createTimeStart)) {
				whereSql.append(" AND ").append(aliasName).append("_create_time > ?");
				paramList.add(createTimeStart);
			}
			if(StringUtils.isNotBlank(createTimeEnd)) {
				whereSql.append(" AND ").append(aliasName).append("_create_time < ?");
				paramList.add(createTimeEnd);
			}
		}
		return whereSql.toString();
	}
	
	/**
	 *@Title: assemblyPageWhereConditon
	 *@Description: 组装where条件部分
	 *@return void
	 *@author youjun
	 *@date 2017年6月24日 下午7:41:33
	 *@param outStorage
	 *@param sql
	 *@param paramList
	 *@param isExistWhereCondition 是否已经存在where条件,true则追加" AND ..."否则追加" WHERE 1=1"
	 *@param alias 别名
	 *@param otherParamMap 
	 * @return 
	 * @throws DaoException 
	 */
	private String assemblyPageWhereConditon(final OutStorage outStorage, final List<Object> paramList, 
			final boolean isExistWhereCondition, final String alias, Map<String, Object> otherParamMap) throws DaoException {
		
		String aliasName = ""; // 别名+".",eg:a.
		if(StringUtils.isNotBlank(alias)) {
			aliasName = new StringBuilder(alias).append(".").toString();
		}
		if(paramList==null) {
			throw new DaoException("参数列表不能为null");
		}
		StringBuilder whereSql = new StringBuilder();
		if(!isExistWhereCondition) {
			whereSql.append(" WHERE 1=1");
		}
		if(outStorage!=null) {
			if(outStorage.getChannelType()!=null) {
				whereSql.append(" AND ").append(aliasName).append("_channel_type = ?");
				paramList.add(outStorage.getChannelType());
				/*whereSql.append(" AND ").append(aliasName).append("_dealer_id = ?");
				paramList.add(outStorage.getDealerId());*/
				
				/*whereSql.append(" AND ").append(aliasName).append("_send_dealer_id = ?");
				paramList.add(outStorage.getSendDealerId());*/
			}
			if (outStorage.getStatus() != null) {
				if(outStorage.getStatus().intValue()==OutStorageStatusEnum.SPECIAL_FOR_PDA_SYNC_LIST.getId()) {
					whereSql.append(" AND ").append(aliasName).append("_status in (");
					final List<Integer> specialForPdaSyncList = new ArrayList<Integer>();
					specialForPdaSyncList.add(OutStorageStatusEnum.HAS_AUDIT.getId());
					specialForPdaSyncList.add(OutStorageStatusEnum.PROCEED_ING.getId());
					specialForPdaSyncList.add(OutStorageStatusEnum.COMPLETE.getId());  //添加已完成
					specialForPdaSyncList.add(OutStorageStatusEnum.EXCEPTION.getId());
					final StringBuilder statusIn = new StringBuilder();
					for(final Integer status:specialForPdaSyncList){
						statusIn.append("?").append(",");
						paramList.add(status);
					}
					whereSql.append(statusIn.substring(0, statusIn.length()-1));
					whereSql.append(")");
				}else{
					whereSql.append(" AND ").append(aliasName).append("_status = ?");
					paramList.add(outStorage.getStatus());
				}
			}
			if (StringUtils.isNotBlank(outStorage.getOrderCode())) {
				whereSql.append(" AND ").append(aliasName).append("_order_code LIKE ?");
				paramList.add(new StringBuilder().append("%").append(outStorage.getOrderCode()).append("%"));
			}
		}
		if(!CollectionUtils.isEmpty(otherParamMap)) {
			final String createTimeStart = (String) otherParamMap.get("createTimeStart");
			final String createTimeEnd = (String) otherParamMap.get("createTimeEnd");
			if(StringUtils.isNotBlank(createTimeStart)) {
				whereSql.append(" AND ").append(aliasName).append("_create_time > ?");
				paramList.add(createTimeStart);
			}
			if(StringUtils.isNotBlank(createTimeEnd)) {
				whereSql.append(" AND ").append(aliasName).append("_create_time < ?");
				paramList.add(createTimeEnd);
			}
		}
		return whereSql.toString();
	}
	
	
	
	/**
	 * (non-Javadoc)
	 *@Title: findPartColumnListByCondition
	 *@Description: 
	 *@see com.sigmatrix.sc.handheld.inter.dao.OutStorageDao#findPartColumnListByCondition(com.sigmatrix.sc.handheld.inter.entity.OutStorage)
	 *@author youjun
	 *@date 2017年6月24日 下午7:50:05
	 */
	@Override
	public List<OutStorageData> findPartColumnListByCondition(final OutStorage outStorage, final String enterpriseCode) throws DaoException {
		
		final StringBuilder sql = new StringBuilder("SELECT _id, _order_code, _warehouse_id, _to_warehouse_id, _dealer_id, _target_channel_id,_channel_type, _status, _remarks, _plan_number, _plan_sp_num, _actual_number, _actual_sp_num, _create_time, _send_dealer_id FROM ");
		sql.append(getTname(enterpriseCode, TABLE_NAME));
		final List<Object> paramList = new ArrayList<Object>();
		sql.append(" WHERE _status <> ?");
		paramList.add(Cons.STATUS_DELETE);
		try {
			sql.append(assemblyWhereConditon(outStorage, paramList, true, "", null));
			sql.append(" ORDER BY _create_time DESC");
			return jdbcTemplate.query(sql.toString(), paramList.toArray(), new RowMapper<OutStorageData>() {
				@Override
				public OutStorageData mapRow(final ResultSet resultSet, final int rowNum) throws SQLException {
					final OutStorageData outStorage = new OutStorageData();
		            outStorage.setOutStorageId(resultSet.getLong("_id"));
					outStorage.setOrderCode(resultSet.getString("_order_code"));
					outStorage.setWarehouseId(resultSet.getInt("_warehouse_id"));
					outStorage.setEntryWarehouseId(resultSet.getInt("_to_warehouse_id"));
					outStorage.setDealerId(resultSet.getInt("_dealer_id"));
					outStorage.setDelegatedDealerId(resultSet.getInt("_target_channel_id"));
					outStorage.setChannelType(resultSet.getShort("_channel_type"));
					outStorage.setStatus(resultSet.getShort("_status"));
					outStorage.setRemarks(resultSet.getString("_remarks"));
					outStorage.setPlanNumber(resultSet.getInt("_plan_number"));
					outStorage.setPlanSpNum(resultSet.getInt("_plan_sp_num"));
					outStorage.setActualNumber(resultSet.getInt("_actual_number"));
					outStorage.setActualSpNum(resultSet.getInt("_actual_sp_num"));
					outStorage.setSendDealerId(resultSet.getInt("_send_dealer_id"));
					final Date createTime = resultSet.getTimestamp("_create_time");
					if(createTime!=null) {
						outStorage.setCreateTime(DateUtil.getDateTimeStr(createTime));
					}
		            return outStorage;
				}
			});
		} catch (DataAccessException e) {
			throw new DaoException(e);
		}
	}
	
	
	@Override
	public List<OutStorageData> findPartColumnListByCondition(OutStorage outStorage, String enterpriseCode,
			boolean isMatchUser, int terminalUserId) throws DaoException {
		
		if(!isMatchUser) {
			return findPartColumnListByCondition(outStorage, enterpriseCode);
		}
		final StringBuilder sql = new StringBuilder("SELECT os._id, os._order_code, os._warehouse_id, os._to_warehouse_id, os._dealer_id, os._target_channel_id, os._channel_type, os._status, os._remarks, os._plan_number, os._plan_sp_num, os._actual_number, os._actual_sp_num, os._create_time, os._send_dealer_id,os._customer_type,ostu._out_storage_id, ostu._terminal_user_id FROM ");
		sql.append(getTname(enterpriseCode, TABLE_NAME)).append(" os ");
		sql.append("LEFT JOIN ").append(getTname(enterpriseCode, TableEnum.DEALER_OUT_STORAGE_TERMINAL_USER)).append(" ostu ");
		sql.append(" ON os._id = ostu._out_storage_id");
		final List<Object> paramList = new ArrayList<Object>();
		sql.append(" WHERE os._status <> ?");
		paramList.add(Cons.STATUS_DELETE);	
		sql.append(" AND os._send_dealer_id = ?");
		paramList.add(outStorage.getSendDealerId());
		sql.append(" AND (ostu._terminal_user_id = ? OR os._id NOT IN (SELECT _out_storage_id FROM ");
		sql.append(getTname(enterpriseCode, TableEnum.DEALER_OUT_STORAGE_TERMINAL_USER)).append(" ))");
		paramList.add(terminalUserId);
		try {
			sql.append(assemblyWhereConditon(outStorage, paramList, true, "os", null));
			sql.append(" ORDER BY _create_time DESC");
			LOGGER.info(sql.toString());
			return jdbcTemplate.query(sql.toString(), paramList.toArray(), new RowMapper<OutStorageData>() {
				@Override
				public OutStorageData mapRow(final ResultSet resultSet, final int rowNum) throws SQLException {
					final OutStorageData outStorage = new OutStorageData();
		            outStorage.setOutStorageId(resultSet.getLong("_id"));
					outStorage.setOrderCode(resultSet.getString("_order_code"));
					outStorage.setWarehouseId(resultSet.getInt("_warehouse_id"));
					outStorage.setEntryWarehouseId(resultSet.getInt("_to_warehouse_id"));
					outStorage.setDealerId(resultSet.getInt("_dealer_id"));
					outStorage.setDelegatedDealerId(resultSet.getInt("_target_channel_id"));
					outStorage.setChannelType(resultSet.getShort("_channel_type"));
					outStorage.setStatus(resultSet.getShort("_status"));
					outStorage.setRemarks(resultSet.getString("_remarks"));
					outStorage.setPlanNumber(resultSet.getInt("_plan_number"));
					outStorage.setPlanSpNum(resultSet.getInt("_plan_sp_num"));
					outStorage.setActualNumber(resultSet.getInt("_actual_number"));
					outStorage.setActualSpNum(resultSet.getInt("_actual_sp_num"));
					outStorage.setSendDealerId(resultSet.getInt("_send_dealer_id"));
					outStorage.setCustomerType(resultSet.getShort("_customer_type"));
					final Date createTime = resultSet.getTimestamp("_create_time");
					if(createTime!=null) {
						outStorage.setCreateTime(DateUtil.getDateTimeStr(createTime));
					}
		            return outStorage;
				}
			});
		} catch (DataAccessException e) {
			throw new DaoException(e);
		}
	}
	
	/**
	 *@ClassName: OutStorageRowMapper
	 *@Description: 封装返回的实体
	 *@author youjun
	 *@date 2017-06-24 17:33:53
	 */
	public class OutStorageRowMapper implements RowMapper<OutStorage> {
		public OutStorage mapRow(final ResultSet resultSet, final int rowNum) throws SQLException {
			final OutStorage outStorage = new OutStorage();
			outStorage.setId(resultSet.getLong("_id"));
			outStorage.setOrderCode(resultSet.getString("_order_code"));
			outStorage.setOutStorageTime(resultSet.getTimestamp("_out_storage_time"));
			outStorage.setCreatorId(resultSet.getInt("_creator_id"));
			outStorage.setCreateTime(resultSet.getTimestamp("_create_time"));
			outStorage.setTerminalId(resultSet.getInt("_terminal_id"));
			outStorage.setDealerId(resultSet.getInt("_dealer_id"));
			outStorage.setWarehouseId(resultSet.getInt("_warehouse_id"));
			outStorage.setStatus(resultSet.getShort("_status"));
			outStorage.setActualNumber(resultSet.getInt("_actual_number"));
			outStorage.setPlanNumber(resultSet.getInt("_plan_number"));
			outStorage.setActualSpNum(resultSet.getInt("_actual_sp_num"));
			outStorage.setPlanSpNum(resultSet.getInt("_plan_sp_num"));
			outStorage.setFailNumber(resultSet.getInt("_fail_number"));
			outStorage.setFailSpNum(resultSet.getInt("_fail_sp_num"));
			outStorage.setUpdateId(resultSet.getInt("_update_id"));
			outStorage.setUpdateTime(resultSet.getTimestamp("_update_time"));
			outStorage.setEnableId(resultSet.getInt("_enable_id"));
			outStorage.setEnableTime(resultSet.getTimestamp("_enable_time"));
			outStorage.setAuditId(resultSet.getInt("_audit_id"));
			outStorage.setAuditTime(resultSet.getTimestamp("_audit_time"));
			outStorage.setStartTime(resultSet.getTimestamp("_start_time"));
			outStorage.setEndTime(resultSet.getTimestamp("_end_time"));
			outStorage.setChannelType(resultSet.getShort("_channel_type"));
			outStorage.setRemarks(resultSet.getString("_remarks"));
			outStorage.setCustomerOrderCode(resultSet.getString("_customer_order_code"));
			outStorage.setCustomerType(resultSet.getShort("_customer_type"));
			outStorage.setCustomerId(resultSet.getInt("_customer_id"));
			outStorage.setProducts(resultSet.getString("_products"));
			outStorage.setToWarehouseId(resultSet.getInt("_to_warehouse_id"));
			outStorage.setTargetChannelId(resultSet.getInt("_target_channel_id"));
			outStorage.setOtherTypeId(resultSet.getInt("_other_type_id"));
			outStorage.setRetReason(resultSet.getInt("_ret_reason"));
			outStorage.setIsAutoVerify(resultSet.getShort("_is_auto_verify"));
			outStorage.setOutStorageType(resultSet.getShort("_out_storage_type"));
			outStorage.setDisableId(resultSet.getInt("_disable_id"));
			outStorage.setDisableTime(resultSet.getTimestamp("_disable_time"));
			outStorage.setSendDealerId(resultSet.getInt("_send_dealer_id"));
            return outStorage;
		} 
	}




}
